class ExamController < ApplicationController
  layout 'main'
  before_filter :admin?, :only=>[:new, :show, :create, :destroy, :edit, :update]
  #before_filter :day60?, :only=>[:start, :take]
  before_filter :date15?, :only=>[:start, :take]
  before_filter :certified?, :only=>[:certificate]
  after_filter :post_log, :except=>[:index, :show, :result]

  def edit
    @exam= Exam.find params[:id]
  end
  def update
    @exam= Exam.find params[:id]
    @exam.update_attributes(params[:exam])
    redirect_to :action => "show_single", :id=>@exam.course_id
  end
  def index
    redirect_to :action=>'show'
  end
  def result # no longer use
    @registration_course= RegistrationCourse.find params[:id]
  end
  def confirm_certificate
    @cer = Certificate.find(:first, :conditions=>["ref_code=?", params[:user][:id]])
    begin
      @user= @cer.registration.user
      @course = @cer.registration.syllabus_opening.syllabus.name
      @status = @cer.c_status
    rescue
      @err= true
    end
  end

  def revoke_certificate
    cer = Certificate.find(params[:id])
    cer.c_status = 'C'
    cer.save
    redirect_to :action=>"confirm_certificate", :user=>{:id=>cer.ref_code}
  end

  def active_certificate
    cer = Certificate.find(params[:id])
    cer.c_status = 'A'
    cer.save
    redirect_to :action=>"confirm_certificate", :user=>{:id=>cer.ref_code}
  end

  def shuffle
    shuffle_questions(params[:id])
    #    @exam= Exam.find(params[:id])
    #    @exam_questions = @exam.exam_questions
    #    @seqs= @exam_questions.collect {|q| q.seq}
    #    @seq1s= @seqs.sort_by { rand }
    #    @exam_questions.each { |q| q.update_attribute :seq, @seq1s.pop }
    flash[:notice]= "สลับคำถามเรียบร้อยแล้ว"
    redirect_to :action => "show_single", :id=>@exam.course_id
  end
  def pre_requisite
    @exam= Exam.find params[:id]
  end
  def add_pre_requisite
    if params[:add_id]
      PreRequisite.create :exam_id=>params[:exam_id], :require_id=>params[:add_id]
    else
      flash[:notice]= "ไม่สามารถเพิ่มแบบทดสอบได้"
    end
    redirect_to :action => "pre_requisite", :id=>params[:exam_id]
  end
  def new_pre_requisite
    js= ["$('#add_pre_form').show();"]
    select = []
    exam= Exam.find params[:id]
    pre_requisites= exam.pre_requisites.map {|p| p.require_id}
    count= 0
    Exam.all.each do |e|
      next if e.id==exam.id
      next if pre_requisites.include? e.id
      select << "<option value=#{e.id}>#{e.name}</option>"
      count += 1
    end
    if count==0
      js << "$('#add_pre_form').html('<b>ไม่มีวิชาที่สามารถเพิ่มได้อีกแล้ว</b>');"
      render :text => js.join
    else
      js << "$('#add_id').html('#{select.join}');"
      render :text => js.join
    end
  end
  def destroy_pre
    pre= PreRequisite.first :conditions=>["exam_id=? and require_id=?", params[:exam_id].to_i, params[:require].to_i]
    pre.destroy
    redirect_to :action => "pre_requisite", :id=>params[:exam_id]
  end
  def report
    @registration_courses = RegistrationCourse.find :all, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.absent=? and registrations.user_id=? and registration_courses.percent is not null",false,current_user.id], :order => "id ASC"
    #    # todo: add field to signal passed exam
    #    exams = []
    #    @exam_results.each do |r|
    #      exams << r.course_id unless exams.include? r.course_id
    #    end
    #    @unique_exams = current_user.pass_courses
  end
  def transcript
    @registration_courses = RegistrationCourse.find :all, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.absent=? and registrations.user_id=? and registration_courses.percent is not null",false,current_user.id], :order => "id ASC"
    render :layout=>false
  end
  #  def result
  #    @exam_result= ExamResult.find params[:id]
  #    render :layout => false
  #  end
  def certificate
    @user= current_user
    render :layout => false
  end
  def new
    @exam= Exam.new
    @exam.course_id = params[:id] if params[:id]
  end
  def create
    @exam= Exam.new params[:exam]
    #    start= Time.local params[:exam][:start[d]]
    @exam.user_id= session[:user_id]
    if @exam.save
      gen_exam_detail
      redirect_to :action=>:show_single, :id=>@exam.course_id
    else
      render :action=>:new
    end
  end
  def show
    @courses= Course.all
  end
  def show_single
    @course= Course.find params[:id]
  end
  def destroy
    Exam.destroy params[:id]
    redirect_to :action=>:show_single, :id=>params[:course_id]
  end
  def take
#    @courses= Course.all
    @courses = []
    registration_courses = RegistrationCourse.find :all, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registrations.user_id=?",current_user.id], :order => "id ASC"
    if(registration_courses.present?)
      registration_courses.each do |item|
        if(not @courses.include?(item.course))
          @courses << item.course
        end
      end
    end
  end
  def start
    @exam= Exam.find_by_id params[:id], :include=>[:exam_questions=>:exam_choices]
    schedule = ExamSchedule.find_by_id params[:schedule_id]
    if schedule.questions
      @exam_questions = ExamQuestion.find(:all, :conditions=>["exam_id=? AND id in (#{schedule.questions})",@exam.id], :order=>"id")
    else
      @exam_questions = ExamQuestion.find(:all, :conditions=>["exam_id=? AND use_flag=?",@exam.id, "Y"], :order=>"id")
    end
    registration_course = RegistrationCourse.find :first, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.course_id=? and registration_courses.absent=? and registrations.user_id=? and registrations.created_at<=?",@exam.course.id,false,current_user.id,schedule.start], :order => "id DESC"
    #registration_course = RegistrationCourse.find :first, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.course_id=? and registration_courses.absent=? and registrations.user_id=?",@exam.course.id,false,current_user.id], :order => "id DESC"
    if !session[:accept]
      redirect_to :action => "accept_policy", :id=>params[:id], :schedule_id=>params[:schedule_id]
    elsif !@exam || !schedule || schedule.start > Time.now || schedule.end < Time.now
      flash[:notice]= "ขออภัย แบบทดสอบที่ท่านเลือก ไม่สามารถทำได้ในขณะนี้"
      redirect_to :action => "take"
    elsif registration_course.blank?
      flash[:notice]= "ขออภัย ท่านไม่ได้ลงทะเบียนสำหรับการสอบนี้"
      redirect_to :action => "take"
    elsif registration_course.grade.present?
      flash[:notice]= "ขออภัย วิชานี้ท่านได้สอบและได้ตัดเกรดเป็นที่เรียบร้อยแล้ว"
      redirect_to :action => "take"
    else
      render :template => "exam/start#{@exam.qtype}.haml"
    end
  end

  def accept_policy
    @id = params[:id]
    @schedule_id = params[:schedule_id]
    render :action=>"accept_policy", :layout=>"main"
  end

  def check_accept_policy
    session[:accept] = true
    redirect_to :action=>"start", :id=>params[:id], :schedule_id=>params[:schedule_id]
  end

  # real method to execute after user submit exam, 0 identify qtype = true/false
  def end_exam0
    @unanswer, @correct, @incorrect = 0,0,0
    @exam= Exam.find params[:id], :include=>[:exam_questions]
    @exam_schedule = ExamSchedule.find params[:schedule_id]
    if @exam_schedule.questions
      @exam_questions = ExamQuestion.find(:all, :conditions=>["exam_id=? AND id in (#{@exam_schedule.questions})",@exam.id], :order=>"id")
    else
      @exam_questions = ExamQuestion.find(:all, :conditions=>["exam_id=? AND use_flag=?",@exam.id, "Y"], :order=>"id")
    end
    #regis_course = RegistrationCourse.find :first, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.course_id=? and registration_courses.absent=? and registrations.user_id=? and registrations.created_at<=?",@exam.course.id,false,current_user.id,@exam_schedule.start], :order => "id DESC"
    #regis_course = RegistrationCourse.find :first, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.course_id=? and registration_courses.absent=? and registrations.user_id=?",@exam.course.id,false,current_user.id], :order => "id DESC"
    #@registration_course = RegistrationCourse.find regis_course.id if regis_course.present?
    regis_course = RegistrationCourse.find_by_sql("select registration_courses.id as id from registration_courses inner join registrations on registration_courses.registration_id = registrations.id inner join syllabus_openings on registrations.syllabus_opening_id = syllabus_openings.id where registration_courses.course_id="+@exam.course.id.to_s+" and registration_courses.absent=false and registrations.user_id="+current_user.id.to_s+" order by syllabus_openings.id DESC, registrations.id DESC, registration_courses.id DESC")
    @registration_course = RegistrationCourse.find regis_course[0].id if regis_course.present?
    if @registration_course.percent
      flash[:notice]= "ขออภัย ท่านสามารถส่งคำตอบได้เพียงครั้งเดียวเท่านั้น"
      #redirect_to :action => "/"
      render :text=>"ขออภัย ท่านสามารถส่งคำตอบได้เพียงครั้งเดียวเท่านั้น", :layout=>"main"
    elsif Time.now <= @exam_schedule.end
      @total = @exam_questions.count
      multiplier = Array.new
      @exam_questions.each do |q|
        multiplier = multiplier << 1.0
      end
      if @exam_schedule.multiplier
        multiplier = @exam_schedule.multiplier.split(',')
      end
      @score = 0.0
      @max = 0.0
      @exam_questions.each_with_index do |q,i|
        @max += multiplier[i].to_f
        if params["answer_#{q.id}"]
          if q.correct == params["answer_#{q.id}"].to_i
            @correct += 1
            @score += multiplier[i].to_f
          else
            @incorrect += 1
          end
        else
          @unanswer += 1
        end
      end
      @percent = 100.0*@score/@max
      #@exam_result= ExamResult.create :user_id=> current_user.id,
      #  :exam_id => @exam.id, :correct => @correct, :incorrect => @incorrect,
      #  :unanswer => @unanswer, :answers => params, :total => @total,
      #  :name => @exam.name, :course_id => @exam.course_id, :percent=>@percent ,
      #  :exam_schedule_id => @exam_schedule.present? ? @exam_schedule.id : nil, :registration_id => @registration_course.present? ? @registration_course.registration.id : nil
      @registration_course.update_attributes :correct => @correct, :incorrect => @incorrect,
        :unanswer => @unanswer, :answers => params, :total => @total,
        :course_id => @exam.course_id, :percent=>@percent ,
        :exam_schedule_id => @exam_schedule.present? ? @exam_schedule.id : nil, :registration_id => @registration_course.present? ? @registration_course.registration.id : nil
      #shuffle_questions(@exam.id)
      #mark if user pass all 5 exams
      #current_user.certified?
      render :template => "exam/end_exam.haml"
    else
      flash[:notice]= "ขออภัย ไม่สามารถส่งคำตอบได้เนื่องจากหมดเวลาแล้ว"
      #redirect_to :action => "/"
      render :text=>"ขออภัย ไม่สามารถส่งคำตอบได้เนื่องจากหมดเวลาแล้ว", :layout=>"main"
    end
  end

  def exam_schedule
    course = Course.find(params[:id])
    @teachers = User.find :all, :conditions=>["role like ?",'%T%'], :order=>'fname ASC, lname ASC'
    @exams = Exam.find_all_by_course_id course.id
    @exam_schedule = ExamSchedule.find_by_course_id course.id, :order=>'id DESC'
    if !@exam_schedule
      @exam_schedule = ExamSchedule.new
      @exam_schedule.course = course
    end
  end

  def total_question
    if params[:exam_id]=="0"
      render :text=>"กรุณาเลือกชุดข้อสอบให้ถูกต้อง", :layout=>false
    else
      @selected_exam = Exam.find(params[:exam_id])
      @knowledges = @selected_exam.group_name.split(",")
      render :action=>"total_question", :layout=>false
    end
  end

  def manage_exam_schedule
    course = Course.find(params[:exam_schedule][:course_id])
    @exam_schedule = ExamSchedule.find_by_course_id course.id, :order=>'id DESC'
    now = Time.now
    if !@exam_schedule
      @exam_schedule = ExamSchedule.new
      #@exam_schedule.course = course
    else
      if now >= @exam_schedule.start and now <= @exam_schedule.end
        flash[:notice] = "ขณะนี้เป็นเวลาสอบของวิชานี้ ท่านไม่สามารถกำหนดเวลาสอบใหม่ได้จนกว่าจะสิ้นสุดเวลาสอบ"
        redirect_to :action => "exam_schedule", :id => course.id
        return
      elsif now > @exam_schedule.end
        @exam_schedule = ExamSchedule.new
        #@exam_schedule.course = course
      end
    end
    @exam_schedule.update_attributes(params[:exam_schedule])
#    if "#{params[:exam_schedule][:total_question]}".empty?
#      eqs = ExamQuestion.find(:all, :conditions=>["exam_id=?", @exam_schedule.exam_id], :order=>"id")
#      eqs.each do |eq|
#        eq.use_flag = 'Y'
#        eq.save
#      end
#      @exam_schedule.total_question = eqs.size
      @exam_schedule.questions = nil
      @exam_schedule.save
#    else
      #set_exam_question(@exam_schedule.id)
      exam = Exam.find(@exam_schedule.exam_id)
      group_name = exam.group_name.split(',')
      for i in 0..exam.total_group-1 do
        total_q = params["q_#{i}".to_sym]
        multiply = params["s_#{i}".to_sym]
        set_exam_question2(@exam_schedule.id, "#{group_name[i]}", total_q.to_i, multiply.to_f)
      end
#    end
    flash[:notice] = "ได้กำหนดเวลาสอบของวิชา "+course.name+" เป็นที่เรียบร้อยแล้ว"
    redirect_to :controller => 'course', :action => "admin"
  end

  def set_exam_question2(exam_schedule_id, group_id, total_question, multiply)
    es = ExamSchedule.find(exam_schedule_id)
    e = Exam.find(es.exam_id)
    eqs = ExamQuestion.find(:all, :conditions=>["exam_id=? AND group_id=?", e.id, group_id])
    eqs.each do |eq|
      eq.use_flag = 'N'
      eq.save
    end
    all_question = eqs.size
    expected_difficult = 1.0
    if es.expected_difficult
      expected_difficult = es.expected_difficult
    end
    run_difficult = 0.0
    average_run_difficult = 0.0
    run_total = 0
    error_count = 0
    while run_total<total_question do
      remain_question = all_question - run_total
      q_no = 0+rand(remain_question)
      eq = nil
      if average_run_difficult<expected_difficult
        eq = ExamQuestion.find(:first, :conditions=>["use_flag='N' AND exam_id=? AND group_id=? AND difficult>=?", e.id, group_id, e.average_difficult], :order=>"id", :offset=>q_no)
      else
        eq = ExamQuestion.find(:first, :conditions=>["use_flag='N' AND exam_id=? AND group_id=? AND difficult<=?", e.id, group_id, e.average_difficult], :order=>"id", :offset=>q_no)
      end
      if eq
        error_count = 0
        if eq.series_id
          series_questions = ExamQuestion.find(:all, :conditions=>["use_flag='N' AND exam_id=? AND series_id=?", e.id, eq.series_id], :order=>"id")
          if (run_total+series_questions.size)<total_question
            series_questions.each do |sq|
              sq.use_flag = 'Y'
              sq.save
              run_total += 1
              run_difficult = run_difficult + sq.difficult
              average_run_difficult = run_difficult/run_total
            end
          end
        else
          eq.use_flag = 'Y'
          eq.save
          run_total += 1
          run_difficult = run_difficult + eq.difficult
          average_run_difficult = run_difficult/run_total
        end
      else
        error_count += 1
      end
      if error_count>(all_question*3)
        break
      end
      if error_count>(all_question*2)
        average_run_difficult = expected_difficult
      end
    end
    use_eqs = ExamQuestion.find(:all, :conditions=>["use_flag='Y' AND exam_id=? AND group_id=?", e.id, group_id], :order=>"id")
    all_eqs = ExamQuestion.count(:conditions=>["use_flag='Y' AND exam_id=?", e.id])
    old_questions = Array.new
    old_multiplier = Array.new
    if es.questions
      old_questions = es.questions.split(',')
      old_multiplier = es.multiplier.split(',')
    end
    if use_eqs
      questions = use_eqs.map{|ueq| ueq.id}.join(',')
      use_eqs.each do |ue|
        old_multiplier = old_multiplier << multiply
      end
      old_questions = old_questions << questions
      es.questions = old_questions.flatten.join(',')
      es.multiplier = old_multiplier.join(',')
    end
    es.total_question = all_eqs
    es.save
  end

  def set_exam_question(exam_schedule_id)
    es = ExamSchedule.find(exam_schedule_id)
    e = Exam.find(es.exam_id)
    eqs = e.exam_questions
    eqs.each do |eq|
      eq.use_flag = 'N'
      eq.save
    end
    all_question = eqs.size
    expected_difficult = 1.0
    if es.expected_difficult
      expected_difficult = es.expected_difficult
    end
    total_question = es.total_question
    run_difficult = 0.0
    average_run_difficult = 0.0
    run_total = 0
    while run_total<total_question do
      error_count = 0
      q_no = 1+rand(all_question)
      eq = nil
      if average_run_difficult<expected_difficult
        eq = ExamQuestion.find(:first, :conditions=>["use_flag='N' AND exam_id=? AND difficult>=?", e.id, e.average_difficult], :order=>"id", :offset=>q_no)
      else
        eq = ExamQuestion.find(:first, :conditions=>["use_flag='N' AND exam_id=? AND difficult<=?", e.id, e.average_difficult], :order=>"id", :offset=>q_no)
      end
      if eq
        if eq.series_id
          series_questions = ExamQuestion.find(:all, :conditions=>["use_flag='N' AND exam_id=? AND series_id=?", e.id, eq.series_id], :order=>"id")
          if (run_total+series_questions.size)<total_question
            series_questions.each do |sq|
              sq.use_flag = 'Y'
              sq.save
              run_total += 1
              run_difficult = run_difficult + sq.difficult
              average_run_difficult = run_difficult/run_total
            end
          end
        else
          eq.use_flag = 'Y'
          eq.save
          run_total += 1
          run_difficult = run_difficult + eq.difficult
          average_run_difficult = run_difficult/run_total
        end
      else
        error_count += 1
      end
      if error_count>(all_question*2)
        break
      end
    end
    use_eqs = ExamQuestion.find(:all, :conditions=>["use_flag='Y' AND exam_id=?", e.id], :order=>"id")
    es.questions = use_eqs.map{|ueq| ueq.id}.join(',')
    es.save
  end

  def grading
    @course = Course.find(params[:id])
    @exam_schedules = ExamSchedule.find_all_by_course_id @course.id, :order=>'id DESC'
  end

  def grade_type
    @exam_schedule = ExamSchedule.find params[:id]
    if(@exam_schedule.teacher_id!=current_user.id)
      flash[:notice] = "ท่านไม่ได้เป็นอาจารย์ผู้ควบคุมการวัดผลการสอบครั้งนี้"
      redirect_to :action => "grading", :id => @exam_schedule.course.id
      return
    end
  end

  def show_scores
    @exam_schedule = ExamSchedule.find params[:id]
    @registration_courses = RegistrationCourse.find_all_by_exam_schedule_id @exam_schedule.id
    render :layout => false
  end

  def practise_scores
    @exam_schedule = ExamSchedule.find params[:id]
    @registration_courses = RegistrationCourse.find_all_by_exam_schedule_id @exam_schedule.id
    render :layout => false
  end

  def assign_practise_scores
    params[:registration_course].keys.each do |i|
      registration_course = RegistrationCourse.find i
      registration_course.practise = params[:registration_course][i]
      registration_course.save
    end
    flash[:notice] = "ได้กำหนดคะแนนภาคปฏิบัติเป็นที่เรียบร้อยแล้ว"
    redirect_to :action => "practise_scores", :id => params[:id]
  end

  def level
    @exam_schedule = ExamSchedule.find params[:schedule_id]
    render :layout => false
  end

  def zscore
    @exam_schedule = ExamSchedule.find params[:schedule_id]
    render :layout => false
  end

  def satisfy
    @exam_schedule = ExamSchedule.find params[:schedule_id]
    render :layout => false
  end

  def gen_grade
    require 'statsample'
    @exam_schedule = ExamSchedule.find params[:id]
    @exam_schedule.update_attributes(params[:exam_schedule])
    if Time.now < @exam_schedule.end
      flash[:notice]= "ขออภัย ไม่สามารถตัดเกรดได้เนื่องจากยังไม่หมดเวลาสอบ"
      redirect_to :action => "grade_type", :id => @exam_schedule.id
      return
    else
      if @exam_schedule.score_type.blank?
        flash[:notice]="ท่านต้องกำหนดรูปแบบการตัดเกรด"
        redirect_to :action => "grade_type", :id => @exam_schedule.id
        return
      end
      if @exam_schedule.score_type == 1 #1=level, 2=Z-Score, 3=Satisfy
        if @exam_schedule.score_a.blank? or @exam_schedule.score_b.blank? or @exam_schedule.score_c.blank? or @exam_schedule.score_d.blank?
          flash[:notice]="ท่านต้องกำหนดช่วงคะแนนในการตัดเกรดให้ครบ"
          redirect_to :action => "grade_type", :id => @exam_schedule.id
          return
        end
      end
      if @exam_schedule.score_type == 2 #1=level, 2=Z-Score, 3=Satisfy
        if @exam_schedule.z_score_a.blank? or @exam_schedule.z_score_b.blank? or @exam_schedule.z_score_c.blank? or @exam_schedule.z_score_d.blank?
          flash[:notice]="ท่านต้องกำหนดช่วงค่า z-score ในการตัดเกรดให้ครบ"
          redirect_to :action => "grade_type", :id => @exam_schedule.id
          return
        end
      end
      if @exam_schedule.score_type == 3 #1=level, 2=Z-Score, 3=Satisfy
        if @exam_schedule.score_a.blank?
          flash[:notice]="ท่านต้องกำหนดช่วงคะแนนในการตัดเกรด"
          redirect_to :action => "grade_type", :id => @exam_schedule.id
          return
        end
      end
      if @exam_schedule.score_type == 2 #1=level, 2=Z-Score, 3=Satisfy
        scores = []
        @exam_schedule.registration_courses.each do |result|
          if @exam_schedule.practise_flag==true
            if result.percent.present?
              if result.practise.present?
                scores << (result.percent*(100-@exam_schedule.practise_percent)/100) + result.practise
              else
                scores << (result.percent*(100-@exam_schedule.practise_percent)/100)
              end
            else
              if result.practise.present?
                scores << result.practise
              end
            end
          else
            scores << result.percent if result.percent
          end
        end
        if scores.size < 2
          flash[:notice]="การตัดเกรดแบบ z-score ไม่สามารถทำได้เนื่องจากมีผู้เข้าสอบน้อยกว่า 2 คน"
          redirect_to :action => "grade_type", :id => @exam_schedule.id
          return
        end
        if scores != []
          scores = scores.to_scale
          mean = scores.mean
          sd = scores.sd
          #render :text=>'mean :'+mean+' sd : '+sd
          zscore_a = Distribution::Normal.p_value(1-(@exam_schedule.z_score_a/100.0))
          zscore_b = Distribution::Normal.p_value(1-(@exam_schedule.z_score_a/100.0)-(@exam_schedule.z_score_b/100.0))
          zscore_c = Distribution::Normal.p_value(1-(@exam_schedule.z_score_a/100.0)-(@exam_schedule.z_score_b/100.0)-(@exam_schedule.z_score_c/100.0))
          zscore_d = Distribution::Normal.p_value(1-(@exam_schedule.z_score_a/100.0)-(@exam_schedule.z_score_b/100.0)-(@exam_schedule.z_score_c/100.0)-(@exam_schedule.z_score_d/100.0))
          @exam_schedule.score_a = (zscore_a * sd) + mean
          @exam_schedule.score_b = (zscore_b * sd) + mean
          @exam_schedule.score_c = (zscore_c * sd) + mean
          @exam_schedule.score_d = (zscore_d * sd) + mean
          @exam_schedule.grading_finish = true
          if @exam_schedule.save
            flash[:notice]= "ได้จัดระดับคะแนนเป็นที่เรียบร้อยแล้ว"
          else
            flash[:notice]= "ไม่สามารถจัดระดับคะแนนได้ mean : #{mean}  s.d. : #{sd}"
            redirect_to :action => "grade_type", :id => @exam_schedule.id
            return
          end
        else
          flash[:notice]= "ไม่พบคะแนนผู้เข้าสอบในรอบการทดสอบนี้"
        end
      end
      ### stamp grade ###
      @exam_schedule.registration_courses.each do |result|
        score = nil
        if @exam_schedule.practise_flag==true
          if result.percent.present?
            if result.practise.present?
              score = (result.percent*(100-@exam_schedule.practise_percent)/100) + result.practise
            else
              score = (result.percent*(100-@exam_schedule.practise_percent)/100)
            end
          else
            if result.practise.present?
              score = result.practise
            end
          end
        else
          score = result.percent if result.percent
        end
        if score.present?
          if @exam_schedule.score_type == 3
            if score >= @exam_schedule.score_a
              result.grade = 'S'
            else
              result.grade = 'U'
            end
          else
            if score >= @exam_schedule.score_a
              result.grade = 'A'
            elsif score >= @exam_schedule.score_b
              result.grade = 'B'
            elsif score >= @exam_schedule.score_c
              result.grade = 'C'
            elsif score >= @exam_schedule.score_d
              result.grade = 'D'
            else
              result.grade = 'F'
            end
          end
          result.save
          begin
            check_user_certificate(result.registration.user_id, result.registration.syllabus_opening.syllabus_id)
          rescue
          end
        end
      end
      ### stamp absent ###
      @absent_regis_courses = RegistrationCourse.find :all, :conditions=>["course_id = ? and created_at <= ? and percent is null", @exam_schedule.course.id, @exam_schedule.start]
      if @absent_regis_courses.any?
        @absent_regis_courses.each do |item|
          item.absent = true
          item.save
        end
      end
    end
    @exam_schedule.grading_finish = true
    @exam_schedule.save
    flash[:notice]= "ได้จัดระดับคะแนนเป็นที่เรียบร้อยแล้ว"
    redirect_to :action => "grading", :id => @exam_schedule.course.id
  end

  def check_user_certificate(user_id, syllabus_id)
    @syllabus = Syllabus.find(syllabus_id)
    user = User.find(user_id)
    #@registration_courses = RegistrationCourse.find :all, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.absent=? and registrations.user_id=? and registration_courses.percent is not null",false,current_user.id], :order => "id ASC"
    result = Registration.find_by_sql "select r.id from registrations r inner join syllabus_openings so on r.syllabus_opening_id = so.id inner join syllabuses s on so.syllabus_id = s.id where s.id="+@syllabus.id.to_s+" and r.user_id="+user.id.to_s
    if(result.present?)
      @registration = Registration.find result[0].id
      @mapper = SyllabusCourseMapper.find_all_by_syllabus_id @syllabus.id
      @unpass_course = []
      @mapper.each do |item|
#        registration_course = RegistrationCourse.find :first, :conditions=>["registration_id=? and course_id=?",@registration.id,item.course.id]
        registration_course = nil
        tmp_registration_course = RegistrationCourse.find_by_sql("select registration_courses.id as id, coalesce(registration_courses.grade, 'Z') as grade from registration_courses inner join registrations on registration_courses.registration_id = registrations.id and registration_courses.course_id = "+item.course.id.to_s+" and registrations.user_id = "+current_user.id.to_s+" order by grade ASC")
        if tmp_registration_course.present?
          registration_course = RegistrationCourse.find_by_id(tmp_registration_course[0].id)
        end
        if(registration_course.blank?)
          #flash[:notice] = 'ท่านลงเรียนไม่ครบทุกกระบวนวิชาของหลักสูตรนี้'
          #          redirect_to :action => 'certificate_request'
          #return
        else
          cert_course_rule = CertificateCourseRule.find :first, :conditions=>["syllabus_id=? and course_id=?",@syllabus.id,item.course.id]
          if(cert_course_rule.present?)
            if(registration_course.grade.present? and cert_course_rule.minimum_grade.present? and registration_course.grade > cert_course_rule.minimum_grade)  # A < B < C < D < F หรือ S < U เงื่อนไขคือ ตก
              @unpass_course << registration_course
            elsif(registration_course.grade.blank?)
              @unpass_course << registration_course
            end
          end
        end
      end
      if(@unpass_course.size()>0)
        #flash[:notice] = 'ท่านไม่ผ่านบางกระบวนวิชาในหลักสูตรนี้'
        #        redirect_to :action => 'certificate_request',
        #return
      else
        cert = Certificate.find_by_registration_id(@registration.id)
        if(cert.blank?)
          year = (Date.today).year+543
          max = 1
          cert_item = Certificate.find_by_sql("select max(ref_code) as ref_code from certificates where ref_code like '%/"+year.to_s+"'")
          if(cert_item.present?)
            old_ref_code = cert_item.collect(&:ref_code)
            if old_ref_code[0].present?
              id, year = old_ref_code[0].split("/")
              max = id.to_i+1
            end
          end
          @ref_code = max.to_s+'/'+year.to_s
          certificate = Certificate.new
          certificate.registration = @registration
          certificate.ref_code = @ref_code
          certificate.save
        else
          @ref_code = cert.ref_code
        end
        #flash[:notice] = 'ท่านผ่านหลักสูตร<br/>รหัสใบรับรอง :: '+@ref_code
        #        redirect_to :action => 'certificate_request'
      end
    else
      #flash[:notice] = 'ไม่พบการลงทะเบียนหลักสูตรที่ท่านเลือก'
      #redirect_to :action => 'certificate_request'
    end
  end

  private
  def gen_exam_detail
    require 'spreadsheet/excel'
    #workbook = Spreadsheet::ParseExcel.parse(@exam.excel.path)
    workbook = Spreadsheet.open @exam.excel.path
    worksheet = workbook.worksheet(0)
    seq= 1
    #skip first row for header
    total_difficult = 0.0
    total_question = 0
    group_array = Array.new
    group_name_array = Array.new
    worksheet.each(1) do |row|
      #exam_question = ExamQuestion.new :seq=>seq, :exam_id=>@exam.id,
      #  :content=>row.at(0).to_s('utf-8'), :correct=>row.at(1).to_i
      difficult = 1
      if row[1]
        difficult = row[1]
      end
      group_id = 0
      if row[2]
        group_id = row[2]
      end
      group_name_array = group_name_array << "#{group_id}"
      group_array = group_array << group_id
      exam_question = ExamQuestion.new :seq=>seq, :exam_id=>@exam.id,
        :content=>row[0], :correct=>row[4], :difficult=>difficult, :series_id=>row[3], :group_id=>group_id
      exam_question.save

      total_difficult += difficult
      total_question += 1

      seq += 1
      # skip first 2 columns for question and correct answer
      skip= 5 ; seq_choice= 1
      row.each do |cell|
        if skip > 0
          skip -= 1
          next
        end
        #exam_choice= ExamChoice.new :seq=>seq_choice, :exam_question_id=>exam_question.id,
        #  :content=> cell.to_s('utf-8')
        exam_choice= ExamChoice.new :seq=>seq_choice, :exam_question_id=>exam_question.id,
          :content=> cell
        exam_choice.save
        seq_choice += 1
      end
    end
    if total_question>0
      @exam.average_difficult = total_difficult/total_question
      @exam.total_group = group_array.uniq.size
      @exam.group_name = group_name_array.uniq.join(',')
      @exam.save
    end
  end

  def shuffle_questions(id)
    @exam= Exam.find(id)
    @exam_questions = @exam.exam_questions
    @seqs= @exam_questions.collect {|q| q.seq}
    @seq1s= @seqs.sort_by { rand }
    @exam_questions.each { |q| q.update_attribute :seq, @seq1s.pop }
  end
  def certified?
    if current_user && current_user.certified?
      return true
    else
      redirect_to "/"
    end
  end
end
